<?php
/* 
 *对帐
 */
class ReconcileAction extends CommonAction {
    public function _filter(&$map) {
      $key = trim($_REQUEST['keywords']);
        if(!empty($key)) {
                $where['sale_no'] = array('eq',$key);//sale_no
                $where['mall_no'] = array('eq',$key);//mall_no
                $where['overview'] = array('like',"%".$key."%");
                $where['mall_overview'] = array('like',"%".$key."%");
            
            $where['_logic'] = "or";
            $map['_complex'] = $where;
        }

        if( $_REQUEST['is_pure'] != '' )
            $map['is_pure'] = $_REQUEST['is_pure'];
      //time 
        $cond_time = array();
        if( $_REQUEST['start_time'] != '' )
          array_push($cond_time, array( 'egt' , strtotime($_REQUEST['start_time']) ) );
          //$cond_time = array( 'egt' , strtotime($_REQUEST['start_time']) );
        if( $_REQUEST['end_time'] != '' )
          array_push($cond_time, array( 'elt' , strtotime($_REQUEST['end_time']) ) );

        //选择了时间类型,并且时间不为空，则按时间进行搜索
        if( $_REQUEST['time_type'] != "" && 
          ($_REQUEST['end_time'] != '' ||  $_REQUEST['start_time'] != '' ) ){
            switch ($_REQUEST['time_type']) {
              case '1': //发货时间
                $map['shipping_time'] = $cond_time;
                break;
              case '2': //收货时间
                $map['receive_time'] = $cond_time;
                break;
              case '3': //收货时间
                $map['create_time'] = $cond_time;
                break;
            }
          }
    }

    public function index()
    {
      $map = $this->_search ();
        if (method_exists ( $this, '_filter' )) {
            $this->_filter ( $map );
        }
        if( isset($_REQUEST['reconcile_status'])&& $_REQUEST['reconcile_status'] != '' && $_REQUEST['reconcile_status'] == 0 ){ //未对帐的，则从sale中来
            $total = 0;
            $model = D('Sale');
        }else{ 
            $total = M('Reconcile')->where($map)->sum('check_total');
            $model = D ('Reconcile');
        }
         $this->assign('check_total',$total); //对账总额
        $this->_list($model, $map);
      
      //pass parameters to export_order function
      $parameters = str_replace(__URL__.'?', '', __SELF__);
      $this->assign('parameters' ,$parameters);
      $this->display ();
      return;
    }

    //导出搜索结果订单
    public function export_order($value='')
    {
       //替换查询的函数，可以得map.再导出结果
      //file type: order,order_detail,both
      $map = $this->_search ();
      $this->_filter ( $map );
      $line = "";
      $data = array();
      if(!is_array($map))
        $this->error('请调整搜索条件，重新导出订单！');
      if( isset($_REQUEST['reconcile_status']) && $_REQUEST['reconcile_status'] != '' && $_REQUEST['reconcile_status'] == 0 ) //未对帐的，则从sale中来
          $voList = D('SaleView')->where($map)->group('sale_id')->findAll( );
      else
          $voList = D('Reconcile')->where($map)->findAll( );
       $order_source = S('order_source');
       $title ="订单号,原始单号,运费,原始运费,应收款,原始应收款,退款,对账金额,原始摘要,原始单对账状态,对账周期,订单状态,原始订单状态,对账时间,发货时间,纯订单,订单来源\n";
        $title = iconv('utf-8', 'GBK', $title);
        if( is_array($voList) )
        foreach ($voList as $key => $value) {
          $data= array(
             'sale_no' => $value['sale_no'],
             'mall_no' => $value['mall_no'],
             'logifee' => $value['logifee'],
             'mall_logifee' => $value['mall_logifee'],
             'should_pay' => $value['should_pay'],
              'mall_should_pay' => $value['mall_should_pay'],
              'refund' => $value['refund'],
              'check_total' => $value['check_total'],
              'mall_overview' => iconv('utf-8','GBK',$value['mall_overview']),
              'mall_reconcile_status' => iconv('utf-8','GBK', getConfigVal($value['mall_reconcile_status'],'Reconcile_status',0) ),

              'cycle_name' => iconv('utf-8','GBK', getModelName($value['cycle_id'],'Reconcile_cycle') ),
              'order_status' => $value['order_status'] == 2 ? iconv('utf-8','GBK',"等待发货") :
                      iconv('utf-8','GBK', getConfigVal($value['order_status'],'orderstatus',0) ),
             'mall_order_status' => iconv('utf-8','GBK', $value['mall_order_status'] ),
              //收货信息
              'create_time' => toDate($value['create_time']),
              'shipping_time' => toDate($value['shipping_time']),
              'is_pure' => iconv('utf-8','GBK',getConfigVal($value['is_pure'],'pure_lens') ),
              'order_source' => iconv('utf-8','GBK',$order_source[$value['order_source_id']]),
             );
            $line .= build_csv_line($data);
            unset($data);
        } 
        $filename = "reconcile_order".date('Ymd_His').'.csv'; //设置文件名 
        
        export_csv($filename,$title.$line);
    }

      //import reconcile order  暂时不用
    public function import(){
    	if(($_FILES['upload']['name'] == '') ){
          $this->display();
          return;
      }
      if(empty($_POST['cycle_id']))
          $this->error("请选择对账周期");
      
        $sheetData = A('Import')->getExcel();
        $Product = M('Product');
        $product_str = "";
        $k = 0; //count import record
        foreach ($sheetData as $key => $value) {
            if ($key > 1) {//从第二行开始读取
                if(empty($value['A']) )
                   continue;
               $sdata = array(
                   'sale_no' => trim($value['A']),   //订单号
                   'mall_no' => trim($value['B']),   //原单号
                   'out_should_pay' => trim($value['E']),   //对方应付款
                   'out_logifee' => trim($value['F']),   //运费
                   'overview' => trim($value['C']),   //摘要
               );
              
               $_POST['sale_no'][$k] = $sdata['sale_no'];
               $_POST['mall_no'][$k] = $sdata['mall_no'];
               $_POST['out_should_pay'][$k] = $sdata['out_should_pay'];
               $_POST['out_logifee'][$k] = $sdata['out_logifee'];
               $_POST['overview'][$k] = $sdata['overview'];
               $k ++;
        }
      } //TODO 避免重复插入
        $this->mass_insert();
    }

 /*导入淘宝订单文件
    **确定订单状态，对账金额，运费，对账状态
    **
    */
    public function import_taobao()
    {
      if(empty($_POST['order_source_id']))
          $this->error("请选择订单来源");
      
      if(empty($_POST['cycle_id']))
          $this->error("请选择对账周期");
 
      $inputFileName = A('Import')->getCsv();
      if(!strpos($inputFileName, 'OrderList') ){
          $this->error('请上传淘宝订单文件，文件名为OrderList字样');
       }
     
        $sale = M('Sale'); //取最新的订单表 
        $mass_new_reconcile = array(); //store new reconcile data
        $sdata = array(); // get line data from csv
        $i = 0; //统计有多少 条记录成功导入
       
        //创建一个临时表，在500条数据以上时效率倍增，提高10-100倍以上
        $temp_sql = "create temporary table vm_tmp_reconcile (id int(11) NOT NULL auto_increment PRIMARY KEY,sale_no varchar(100) default NULL,mall_no varchar(250),mall_reconcile_status tinyint(1),
            should_pay float(10,2),logifee float(10,2),mall_should_pay float(10,2),mall_logifee float(10,2),goods_money float(10,2),check_total float(10,2),
            overview varchar(200),order_source_id smallint(3),cycle_id smallint(2),order_status tinyint(1),mall_order_status varchar(50),mall_overview varchar(255), shipping_time int(11), create_time int(11),reconcile_time int(11)
            ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;";
        M()->execute($temp_sql);
        $tmp_reconcile = M('Tmp_reconcile');
      
        $row = 1;
      if (($handle = fopen($inputFileName, "r")) !== FALSE) {
          while (($data = fgetcsv($handle, 0, ',', '"', '"')) !== FALSE) {

            if($row > 1 ) { //start from second line
              $reconcile = $sale->where('mall_no = "'.trim($data['0']).'" and reconcile_status = 0' )->field('id')->find();
              if(! is_array($reconcile) ){
                  //check合并订单，类似德生堂的,分隔的订单
                  if( $_POST['order_source_id'] != 5  ) //不是德生堂的订单,则跳过
                      continue;
                  //德生堂合并订单
                  $unin_map['_string'] ='locate("' .trim($data['0']).'",mall_no) > 0 ';
                  $reconcile = $sale->where( $unin_map)->field('id')->find();
                  if(! is_array($reconcile) ){
                      continue; //非隐形眼镜的订单跳过处理
                  }else{//德生堂合并订单
                      //判断合并订单是否在对账表中存在,若存在,则跳过
                       $reconcile_yet = M('Reconcile')->where('mall_no = "'.trim( $data['0'] ).'"')->getField('id');
                       if($reconcile_yet)
                           continue;
                       else//合并订单未对账
                          $reconcile = $sale->where('id = '.$reconcile['id'])->field('sale_no,should_pay,logifee,create_time,shipping_time,shipping_name,create_time,order_status,total')->find();

                  }
              }else //未对过帐
                  $reconcile = $sale->where('id = '.$reconcile['id'] )->field('sale_no,should_pay,logifee,shipping_time,shipping_name,create_time,order_status,total')->find();
                   $sdata = array(
                   'sale_no' => $reconcile['sale_no'],
                   'mall_no' => trim( $data['0'] ),
                   'should_pay' => $reconcile['should_pay'],
                   'logifee' => $reconcile['logifee'],
                   'shipping_time' =>$reconcile['shipping_time'],
                   'create_time' => $reconcile['create_time'], //下单时间
                   'reconcile_time' => time(), //对账时间
                   'overview' =>  ($reconcile['shipping_name'].'create_time:'.toDate($reconcile['create_time']) ),
                   'order_source_id' => $_POST['order_source_id'],
                   'cycle_id' => $_POST['cycle_id'],
                   'order_status' => $reconcile['order_status'],
                   'goods_money' =>$reconcile['total'], //货款
                   'mall_logifee' => trim($data['4']),
                   'mall_should_pay' => trim($data['8']) ,  //其实是实际 付款，包括合并订单款项
                   'mall_order_status' => trim(iconv('GBK','utf-8',$data['10'])) ,
                   //shipping address info
                   'mall_overview' => trim(iconv('GBK','utf-8',$data['12'])).' '.trim($data['17']) ."\n".iconv('GBK','utf-8',$data['19']),
                   
               );
                
              if($reconcile['logifee'] != $sdata['mall_logifee'] )
                $sdata['mall_reconcile_status'] = -2; //运费不匹配
              else if($reconcile['should_pay'] != $sdata['mall_should_pay'] )
                $sdata['mall_reconcile_status'] = -4; //总金额不匹配 ; 货款不匹配TODO
              else if($reconcile['should_pay'] == $sdata['mall_should_pay'] )
                $sdata['mall_reconcile_status'] = 1 ;

              if( $sdata['mall_reconcile_status'] == 1 ) //确定对账金额
                $sdata['check_total'] = $sdata['mall_should_pay'];
              
              //对账金额以交易成功或已经发货为准
              if( $sdata['mall_order_status'] == "交易成功" || $sdata['mall_order_status'] == "卖家已发货，等待买家确认"  || $sdata['mall_order_status'] == "买家已付款，等待卖家发货")
                $sdata['check_total'] = $sdata['mall_should_pay'];
              else{ //其他订单状态 设为对账成功，不再人工对账
                  $sdata['check_total'] = 0;
                  $sdata['mall_reconcile_status'] = 1 ; 
              }
              $tmp_reconcile->add($sdata);
              //订单表设置为已经对帐 德生堂合并订单暂不能设置状态
               $sale->where('mall_no = "'.trim($data['0']).'"')->setField('reconcile_status',$sdata['mall_reconcile_status']);
               unset($sdata);
               $i++;
            }
       $row++;
        }
        if ( $count_rid = $tmp_reconcile->count('id') ){
        //将临时表中的新数据插入到正式的对账表 增加ignore，如果表中存在相应记录，则忽略，继续其他数据插入
        $insert_sql = 'insert ignore  vm_reconcile(sale_no,mall_no,should_pay,logifee,shipping_time,create_time,reconcile_time,overview,order_source_id,cycle_id,order_status,goods_money,mall_logifee,mall_should_pay,mall_order_status,mall_overview,mall_reconcile_status,check_total)
            select sale_no,mall_no,should_pay,logifee,shipping_time,create_time,reconcile_time,overview,order_source_id,cycle_id,order_status,goods_money,mall_logifee,mall_should_pay,mall_order_status,mall_overview,mall_reconcile_status,check_total from vm_tmp_reconcile
            ';
        $i = M()->execute($insert_sql);
        }else
           $i = 0;
        //drop tmp table
       M()->execute('Drop table vm_tmp_reconcile');
        fclose($handle);
        $this->success("成功对账 ".$i." 条淘宝订单",false,5);
          
        }
        
        unlink($inputFileName); //上传后删除文件
    }

    //导入淘宝订单明细文件，区分混和订单
    public function import_taobao_detail()
    {
      if(empty($_POST['order_source_id']))
          $this->error("请选择订单来源");
      $inputFileName = A('Import')->getCsv();
       if(!strpos($inputFileName, 'OrderDetailList') ){
          $this->error('请上传淘宝宝贝明细文件，文件名为ExportOrderDetailList字样');
       }

        $sale_detail = M('Sale_detail2');
        $sale = M('Sale');
        $sdata = array(); // get line data from csv
        $i = 0; //统计有多少 条记录成功导入
       
          $row = 1;
      if (($handle = fopen($inputFileName, "r")) !== FALSE) {
          while (($data = fgetcsv($handle, 0, ',', '"', '"')) !== FALSE) {
            if($row > 1 ) { //start from second line
              
             $out_number = trim($data['4']);//商品编码
             $mall_no = trim($data['0']);
            
            if($_POST['order_source_id'] == 3 ){ //京卫对账，使用产品匹配表映射
               $jw_map = getPmap(1); //京卫匹配的产品列表 Out_number=> my_number
               $product_id = $jw_map[trim($data['4'])];
               if($product_id )
                   continue;
               else{ //非我方产品
                  $reconcile_pure_order = M('Reconcile')->where('mall_no = "'.trim($data['0']).'" and order_source_id= 3' )->getField('is_pure');
                  if(($reconcile_pure_order === NULL) || $reconcile_pure_order == 0 ) //如果不存在，或已经确定是混和订单，则跳过处理
                      continue;
                  M('Reconcile')->where('mall_no = "'.$mall_no.'"')->setField('is_pure',0);
                  $i++;
               }
            }else{//德生堂或亚洲的 对账，以隐形眼镜关键词匹配,判断
               $sale_title = trim(iconv('GBK','utf-8',$data['1']));
               if(is_lens_order($sale_title) === false ){ //非隐形眼镜商品
                  $reconcile_pure_order = M('Reconcile')->where('mall_no = "'.trim($data['0']).'" and order_source_id= '.$_POST['order_source_id'] )->getField('is_pure');
                  if(($reconcile_pure_order === NULL) || $reconcile_pure_order == 0 ) //如果不存在，或已经确定是混和订单，则跳过处理
                      continue;
                  M('Reconcile')->where('mall_no = "'.$mall_no.'"')->setField('is_pure',0);
                  $i++;
               }else 
                   continue;
            }
           }
            $row++;
        }
          fclose($handle);
          $this->success("成功对账 ".$i." 条淘宝订单",false,5);
        }
        unlink($inputFileName); //上传后删除文件
    }

    //导入淘宝退款明细
    public function import_taobao_refund()
    {
      if($_POST['order_source_id'] != 5 && $_POST['order_source_id'] != 7  )
          $this->error("此功能仅处理德生堂或亚洲的淘宝订单!");
      $inputFileName = A('Import')->getCsv();
       if(!strpos($inputFileName, 'RefundList') ){
          $this->error('请上传退款文件，文件名为ExportRefundList字样');
       }

        $i = 0; //统计有多少 条记录成功导入
       $refund_total = 0; //汇总某个订单的退款金额
          $row = 1;
      if (($handle = fopen($inputFileName, "r")) !== FALSE) {
          while (($data = fgetcsv($handle, 0, ',', '"', '"')) !== FALSE) {
            if($row > 1 && trim(iconv('GBK','utf-8',$data['13'])) == "退款成功" ) { //start from second line 
              
             $sale_title = trim(iconv('GBK','utf-8',$data['6']));
             $mall_no = trim($data['0']);
             $refund_no = trim($data['1']);
             $refund_money = trim($data['7']);
            
            if( is_lens_order($sale_title) === false ){
                continue;
            }else{//隐形眼镜商品
                $reconcile = M('Reconcile')->where('mall_no = "'.trim($data['0']).'" and order_source_id='.$_POST['order_source_id'] )->field('id,refund_no,refund,check_total')->find();
                if(!is_array($reconcile) ) //如果不存在，则跳过处理
                   continue;
                if( strpos($reconcile['refund_no'],$refund_no ) !== false )
                    continue; //已经处理过的退款
                $sdata = array(
                    'refund' => ($reconcile['refund'] + $refund_money),
                    'refund_no' => ",".$refund_no,
                    //'check_total' =>( $reconcile['check_total'] - $refund_money),//对账金额=原对账金额-退款 ；不妥，如果交易成功，则会重复扣款
                );
                M('Reconcile')->where('id = "'.$reconcile['id'].'"')->save( $sdata );
                $i++;
            }
           }
            $row++;
        }
          fclose($handle);
          $this->success("成功计算 ".$i." 条淘宝订单 退款",false,5);
        }
        unlink($inputFileName); //上传后删除文件
    }
   //TODO 不使用post，效率会更高些
    public function mass_insert()
    {
      $data = $_POST;

      $mass_data = array(); // mass insert reconcile datas

      $error_order = "未录入订单"; 
      if(empty($_POST['cycle_id']))
          $this->error("请选择对账周期");
        $count_no = count($data['sale_no']);
        $model = M('Reconcile');
        $k = 0 ; //count success import order 
        for($i =0; $i < $count_no;$i++) {
          if($data['sale_no'][$i] ==''  )
            continue;
          $sdata = array();
          $_POST = array(
            'cycle_id' => $data['cycle_id'],
            'sale_no' => $data['sale_no'][$i],
            'mall_no' => $data['mall_no'][$i],
            'out_should_pay' => $data['out_should_pay'][$i],
            'out_logifee' => $data['out_logifee'][$i],
            'overview' => $data['overview'][$i],
            );
          $order = M('Sale')->getBySale_no($_POST['sale_no']);
          if(is_array($order)){
            $_POST['order_status'] = $order['order_status'];
            $_POST['should_pay'] = $order['should_pay'];
             $_POST['logifee'] = $order['logifee'];
             $_POST['goods_money'] = $order['total'];
             $_POST['goods_money'] = $order['total'];
             $_POST['order_source_id'] = $order['order_source_id'];
             $_POST['shipping_time'] = $order['shipping_time'];
             $_POST['create_time'] = time();
             if($order['logifee'] != $_POST['out_logifee'] )
                $_POST['reconcile_status'] = -2; //运费不匹配
             else if($order['should_pay'] != $_POST['out_should_pay'] )
                $_POST['reconcile_status'] = -4; //总金额不匹配 货款不匹配TODO
             else if($order['should_pay'] == $_POST['out_should_pay'] )
                $_POST['reconcile_status'] = 1 ;
              if( $_POST['reconcile_status'] == 1 ) //确定对账金额
                $_POST['check_total'] = $_POST['should_pay'];

                $sdata['reconcile_status'] = $_POST['reconcile_status'];
            //更新订单表
            M('Sale')->where('sale_no = "'.$_POST['sale_no'].'"')->save($sdata);
          }else{
            $_POST['reconcile_status'] = -1; //系统无此单
          }
          //$mass_data[$k] = $_POST;
           //$k ++;
          
          $list=$model->add ($_POST);
          if ($list!==false) {
            $k ++;
          }else{
                $error_order .= ";".$_POST['sale_no'];
          }
          unset($_POST);
        }
        

        $jumpUrl = $_REQUEST['jumpUrl'];
        if(empty($jumpUrl))
            $jumpUrl = Cookie::get ( '_currentUrl_' );
        $this->assign ( 'jumpUrl', $jumpUrl );
        $this->success ('成功导入 '.$k.' 条对账单!<br/>'.$error_order,false,20);
    }


}
?>
